package ai.xiaodao.db;

import ai.xiaodao.config.Const;
import ai.xiaodao.config.DBConfig;
import ai.xiaodao.config.Security;
import ai.xiaodao.trade.LocalOrder;
import com.futu.openapi.pb.TrdCommon;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.log4j.Logger;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.Reader;
import java.sql.*;

/*
  the security localOrders local store operation include record ,update,delete
 */

public class OrderOps {

    public static Connection conn = DBStore.getInstance().getConnection();

    static Logger log = Logger.getLogger(OrderOps.class.getName());

    /**
     * for local storage of the security order after submitted to the broker system
     *
     * @param order the local order class
     */
    static public void orderAdd(LocalOrder order) {
        int result = 0;
        String sql = "insert into order_record  values(?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, order.getOrderID());
            ps.setString(2, order.getCode());
            ps.setInt(3, order.getTrdSide());
            ps.setDouble(4, order.getTrdQty());
            ps.setInt(5, order.getMarket());
            ps.setString(6, order.getDate());
            ps.setDouble(7, order.getPrice());
            ps.setInt(8, order.getStatus());
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if (result != 1) {
            log.error("add local order record error");
        } else {
            log.info("local order record added");
        }

    }

    /**
     * record the sec code ,trade side ,trade qty during the placing the trade request and
     * set the local order status as -2
     *
     * @param order local record order
     */
    static public void addLocalSubmitRecord(LocalOrder order) {
        int result = 0;
        String sql = "insert into order_record(code,trdSide,qty,market,status,price) values(?,?,?,?,?,?)";
        log.info("adding local record when placing word");
        log.debug(order.toString());
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, order.getCode());
            ps.setInt(2, order.getTrdSide());
            ps.setDouble(3, order.getTrdQty());
            ps.setDouble(4, order.getMarket());
            ps.setDouble(5, order.getStatus());
            ps.setDouble(6, order.getPrice());

            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            log.error("adding local record error when placing order : " + throwables.getMessage());
        }
        if (result != 1) {
            log.error("add local order record error");
        } else {
            log.info("local order record added");
        }

    }

    /**
     * create database and table for sqlite
     */
    public void dbCreate() {
        ScriptRunner scriptRunner = new ScriptRunner(conn);
        try {
            log.info(" loading database  initialize sql file  ");
            log.debug(" the database config is " + DBConfig.getDBScriptPath());
            Reader reader = new BufferedReader(new FileReader(DBConfig.getDBScriptPath()));
            log.info(" loading database  initialize sql end   ");
        } catch (FileNotFoundException e) {
            log.error(e.getMessage());
        }


    }

    /**
     * 订单提交后，记录订单(本地)
     *
     * @param localOrder
     * @return
     */
    public static boolean orderRecord(LocalOrder localOrder) {

        log.info(localOrder.toString() + "recorded");
        return false;
    }

    /**
     * 根据订单号，删除本地订单记录
     * delete local order record by order ID
     *
     * @return true if success false if failed
     */

    public static boolean orderDelete(long orderID) {
        log.debug("deleting local order record with orderid " + orderID);
        String sql = "delete order_record where id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, orderID);
            Statement statement = conn.createStatement();
            statement.setQueryTimeout(30);
            statement.executeQuery(sql);
            log.info("deleting local order succeed with orderid " + orderID);
            return true;
        } catch (SQLException e) {
            log.error("delete local order record failed with orderid " + orderID);
            log.error("query database exception " + e.getMessage());
            return false;
        }
    }

    /**
     * 订单状态变更后，更新本地订单状态
     *
     * @param security
     * @return
     */

    public static boolean orderUpdate(Security security) {
        return false;
    }
    /*
    get local order record number
     */

    public static int getOrderNum() {
        Statement statement;
        int result = 0;
        String sql = "select count(*) from order_record";
        try {
            statement = conn.createStatement();
            statement.setQueryTimeout(30);
            ResultSet rs = statement.executeQuery(sql);
            result = rs.getInt(1);
        } catch (SQLException e) {
            log.error(e.getMessage());
        }
        return result;
    }

    /**
     * 将本地的订单记录进行更新
     * 如果有这个订单，则同步服务器上的状态，如果没有这个订单则记录该订单的信息
     * 如果服务器上的状态是下单失败，则删除本地的记录信息
     * <p>
     * sync server order information to local storage
     *
     * @param order the security  order information  from the server
     */
    public static void updateLocalOrderStatus(TrdCommon.Order order) {
        long orderID = order.getOrderID();
        int trdSide = order.getTrdSide();
        int orderStatus = order.getOrderStatus();
        LocalOrder localOrder = orderTransfer(order);
        //如果订单是卖单且下单失败，如果本地有记录则清除没有则记录日志
        if ((trdSide == TrdCommon.TrdSide.TrdSide_Buy_VALUE) &&
                (orderStatus == TrdCommon.OrderStatus.OrderStatus_Cancelled_All.getNumber() ||
                        orderStatus == TrdCommon.OrderStatus.OrderStatus_FillCancelled.getNumber())) {
            //本地有订单
            if (getOrderByOrderID(orderID)) {
                boolean res = updateOrderStatus(order.getOrderStatus(), orderID);
                if (!res) {
                    log.error("order status update failed to orderid " + orderID);
                } else {
                    log.info("order status updated with orderid " + orderID);
                }
            }
            //本地有提交记录，则添加记录,则删除提交记录，添加新的订单记录
            else if (!hasPlaceRecord(localOrder)) {
                rmPlaceRecord(order);
                orderAdd(localOrder);

            }
            //本地没有订单
            else {
                log.debug("adding local order record with orderid " + orderID);
                orderAdd(localOrder);
            }
        }
        //订单结果为下单失败，如果本地有记录，则删除该记录，无记录则记录日志
        else {
            //本地有订单
            if (getOrderByOrderID(orderID)) {
                boolean res = updateOrderStatus(order.getOrderStatus(), orderID);
                if (!res) {
                    log.error("order status update failed to orderid " + orderID);
                } else {
                    log.info("order status updated with orderid " + orderID);
                }
            }
            //本地没有订单，则添加记录
            else {
                log.debug("adding local order record with orderid " + orderID);
                orderAdd(localOrder);
            }

        }

    }

    /**
     * deleting local place order record
     *
     * @param order order of futu system callack
     */
    private static void rmPlaceRecord(TrdCommon.Order order) {
        log.info("deleting local place record");
        log.debug("deleting local place record for " + order.toString());
        String sql = "delete from order_record status = ? and qty=? and  trdSide=? and price=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, Const.PLACE_ORDER_MARK);
            ps.setDouble(2, order.getQty());
            ps.setInt(3, order.getTrdSide());
            ps.setDouble(4, order.getPrice());
            Statement statement = conn.createStatement();
            statement.setQueryTimeout(30);
            statement.executeQuery(sql);
            log.info("deleting local place record succeed");
        } catch (SQLException e) {
            log.error("deleting  place record error  " + e.getMessage());
        }
    }

    /**
     * check whether the data has the place record with the call back order
     * by order status ,trade qty ,trade side ,trade code,trade price
     *
     * @param order the call back order
     * @return true if had false if not
     */
    private static boolean hasPlaceRecord(LocalOrder order) {
        String sql = "select * from order_record where status = ? and qty=? and  trdSide=? and price=? and code=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, Const.PLACE_ORDER_MARK);
            ps.setDouble(2, order.getTrdQty());
            ps.setInt(3, order.getTrdSide());
            ps.setDouble(4, order.getPrice());
            ps.setString(5, order.getCode());
            ResultSet rs = ps.executeQuery();
            int rowCount = DBStore.getRows(rs);
            return rowCount == 1;
        } catch (SQLException e) {
            log.error("query database exception " + e.getMessage());
            return true;
        }
    }


    /**
     * check whether the data has the place record with the call back order
     * by order status ,trade qty ,trade side ,trade code,trade price
     *
     * @param order the call back order
     * @return true if had false if not
     */
    public static boolean hasRecord(LocalOrder order) {
        String sql = "select * from order_record where  qty=? and  trdSide=? and price=? and code=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDouble(1, order.getTrdQty());
            ps.setInt(2, order.getTrdSide());
            ps.setDouble(3, order.getPrice());
            ps.setString(4, order.getCode());
            ResultSet rs = ps.executeQuery();
            int rowCount = DBStore.getRows(rs);
            return rowCount >= 1;
        } catch (SQLException e) {
            log.error("query database exception " + e.getMessage());
            return true;
        }
    }

    /**
     * check wehther local have a buy finished by record which the qty,price,code equal to the selling order
     * and the order status was fully  finished.
     *
     * @param order selling order source from the config
     * @return true if has false if not
     */
    public static boolean hasBuyRecord(LocalOrder order) {
        String sql = "select * from order_record where  qty=? and  trdSide=? and price=? and status=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDouble(1, order.getTrdQty());
            ps.setInt(2, TrdCommon.TrdSide.TrdSide_Buy_VALUE);
            ps.setDouble(3, order.getPrice());
            ps.setInt(4, TrdCommon.OrderStatus.OrderStatus_Filled_All_VALUE);
            ResultSet rs = ps.executeQuery();
            //todo Strictly should only one record
            int rowCount = DBStore.getRows(rs);
            return rowCount == 1;
        } catch (SQLException e) {
            log.error("query database exception " + e.getMessage());
            return true;
        }
    }

    /**
     * 根据订单号查询订单,是否存在，如果存在返回1 ，不存在返回0
     */

    public static boolean getOrderByOrderID(long orderID) {
        String sql = "select * from order_record where id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, orderID);
            Statement statement = conn.createStatement();
            statement.setQueryTimeout(30);
            ResultSet rs = statement.executeQuery(sql);
            int rowCount = DBStore.getRows(rs);
            return rowCount == 1;
        } catch (SQLException e) {
            log.error("query database exception " + e.getMessage());
            return true;
        }
    }

    public static LocalOrder orderTransfer(TrdCommon.Order order) {
        return new LocalOrder(order.getOrderID(), order.getCode(), order.getSecMarket(), order.getTrdSide(),
                order.getQty(), order.getPrice(), order.getCreateTime(), order.getOrderStatus());
    }

    /**
     * 更新订单状态
     *
     * @param status  订单状态
     * @param orderid 订单ID
     * @return
     */

    public static boolean updateOrderStatus(int status, long orderid) {
        int result = 0;
        String sql = "update  order_record  set status = ? where id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, status);
            ps.setLong(2, orderid);
            ps.executeQuery(sql);
            return true;
        } catch (SQLException e) {
            log.error("query database exception " + e.getMessage());
            return false;
        }
    }

    /**
     * 清理本地订单记录,一条卖的订单完成，则删除其本身的记录和对应的买单记录
     *
     * @param orderid
     * @return
     */

    //todo 未实现
    public static boolean clearLocalRecord(long orderid) {
        return false;
    }

}
